Imports Microsoft.VisualBasic



Namespace Persistencia.ModuloRRHH

    Public Class ControladorPersistencia
#Region "Atributos"
        Private Shared instance As ControladorPersistencia
        Private query As String
        Private BD As Persistencia.ModuloRRHH.bd
#End Region
        'Constructor Vacio
        Public Sub New()
            BD = Persistencia.ModuloRRHH.bd.getInstance
        End Sub

#Region "getInstance"
        'Patron Singleton 
        Public Shared Function getInstance() As ControladorPersistencia

            If instance Is Nothing Then
                instance = New ControladorPersistencia
            End If

            Return instance

        End Function
#End Region

#Region "funciones"

        Public Sub InsertarEmpleado(ByVal empleado As Empleado)
            Try
                query = "insert into empleado (rut,edad,fechainicioempresa,fechafinempresa,nombre,apellido,direccion,telefono,profesion,sexo,fechanacimiento," & _
                                   "nacionalidad,paisnacimiento,regionnacimiento,comunanacimiento,ciudadnacimiento,provinciaresidencia,ciudadresidencia,celular," & _
                                   "email,estadocivil,cargasfamiliares,numerocalzado,talla) values ('" & empleado.Rut & "','" & empleado.Edad & "','" & empleado.FechaInicioEmpresa & _
                                   "','" & empleado.FechaFinEmpresa & "','" & empleado.Nombre & "','" & empleado.Apellido & "','" & empleado.Direccion & "','" & _
                                   "','" & empleado.Telefono & "','" & empleado.Profesion & "','" & empleado.Sexo & "','" & empleado.FechaNacimiento & empleado.Nacionalidad & _
                                   "','" & empleado.Paisnacimiento & "','" & empleado.RegionNacimiento & "','" & empleado.ComunaNacimiento & "','" & empleado.CiudadNacimiento & _
                                   "','" & empleado.ProvinciaRecidencia & "','" & empleado.CiudadResidencia & "','" & empleado.Celular & "','" & empleado.Email & "','" & empleado.EstadoCivil & _
                                   "','" & empleado.CargasFamiliares & "','" & empleado.NumeroCalzado & "','" & empleado.Talla & "')"

                BD.EjecutarSinRetorno(query)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub


        Public Sub InsertarEmpleadoProfesion(ByVal rutEmpleado As String, ByVal idProfesion As String, ByVal prioridad As Integer)
            Try
                query = "insert into profesionempleado (fkempleado,fkprofesion,prioridad) values ('" & rutEmpleado & "'," & idProfesion & "," & prioridad & ")"

                BD.EjecutarSinRetorno(query)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub

#End Region

#Region "Obtener"
        Public Function ObtenerUsuarioUserID(ByVal userID As String) As ArrayList
            Try
                Dim query As String = "select * from usuariosistema where usuario='" + userID + "'"
                Return BD.ObtenerUsuario(query)
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Public Function ObtenerUsuario() As ArrayList
            Try
                Dim query As String = "select * from usuariosistema"
                Return BD.ObtenerUsuario(query)
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Public Function ObtenerProfesion() As ArrayList
            Try
                Dim query As String = "select * from profesion"
                Return BD.ObtenerProfesion(query)
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Public Function ObtenerUsuarioRutEmpleado(ByVal rutEmpleado As String) As ArrayList
            Try
                Dim query As String = "select * from usuariosistema where fkempleado='" + rutEmpleado + "'"
                Return BD.ObtenerUsuario(query)
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Public Function ObtenerGrupo() As ArrayList
            Try
                Dim query As String = "select * from grupousuarios"
                Return BD.ObtenerGrupo(query)
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Public Function ObtenerGrupoCriterio(ByVal criterio As String) As ArrayList
            Try
                Dim query As String = "select * from grupousuarios where id=" + criterio
                Return BD.ObtenerGrupo(query)
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Public Function obtenerEmpleadoFK(ByVal fk As String) As ArrayList
            Try
                Dim query As String = "select * from empleado where rut ='" + fk + "'"
                Return BD.obtenerEmpleado(query)
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Public Function obtenerEmpleado() As ArrayList
            Try
                Dim query As String = "select * from empleado"
                Return BD.obtenerEmpleado(query)
            Catch ex As Exception
                Throw ex
            End Try
        End Function


        Public Function obtenerEmpleadoRut(ByVal rut As String) As ArrayList
            Try
                Dim query As String = "select * from empleado where rut='" + rut + "'"
                Return BD.obtenerEmpleado(query)
            Catch ex As Exception
                Throw ex
            End Try

        End Function

        Public Function obtenerEmpleadoEspecialidad(ByVal criterio As String, ByVal op As Integer) As ArrayList
            Dim query As String = ""
            Try
                If op = 1 Then
                    query = "select * from profesionempleado where fkempleado='" + criterio + "' order by prioridad asc"
                End If

                If op = 2 Then
                    query = "SELECT " & _
                            "profesionempleado.fkestacion, " & _
                            "profesionempleado.prioridad, " & _
                            "profesionempleado.fkempleado " & _
                            "FROM " & _
                            "public.profesionempleado, " & _
                            "public.estaciones " & _
                            "WHERE " & _
                            "estaciones.id = profesionempleado.fkestacion and " & _
                            "estaciones.estacion='" & criterio & "' order by prioridad"
                End If

                Return BD.obtenerEmpleadoEspecialidad(query)
            Catch ex As Exception
                Throw ex
            End Try
        End Function
#End Region

#Region "Utilidades"
        Public Function ObtenerDato(ByVal query As String) As Hashtable
            Try
                Return BD.EjecutarConRetorno(query)
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Public Function cuentaSolicitudes() As Hashtable
            Try
                Dim query As String = "select count(*) cuenta from solicitudes where estado ='P'"
                Return BD.EjecutarConRetorno(query)
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Public Function visibilidadesFormsInternos(ByVal nombreUsuario As String) As GrupoUsuario
            Try
                Dim query As String = "select fkgrupousuarios from usuariosistema where usuario ='" + nombreUsuario + "'"
                Dim idGrupo As String = Me.ObtenerDato(query).Item("fkgrupousuarios").ToString
                Dim grupo As GrupoUsuario = DirectCast(Me.ObtenerGrupoCriterio(idGrupo).Item(0), GrupoUsuario)

                Return grupo
            Catch ex As Exception
                Throw ex
            End Try

        End Function

#End Region

    End Class
End Namespace

